/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package unc.pds.seen;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import javax.ejb.EJBException;
import javax.ejb.FinderException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import unc.pds.data.Consts;

/**
 *
 * @author ain
 */
public class SimpleSearchEngine implements SessionBean, PDSSerchEngine {

    private SessionContext context;

    // <editor-fold defaultstate="collapsed" desc="EJB infrastructure methods. Click the + sign on the left to edit the code.">;
    // TODO Add code to acquire and use other enterprise resources (DataSource, JMS, enterprise bean, Web services)
    // TODO Add business methods or web service operations
    /**
     * @see javax.ejb.SessionBean#setSessionContext(javax.ejb.SessionContext)
     */
    public void setSessionContext(SessionContext aContext) {
        context = aContext;
    }

    /**
     * @see javax.ejb.SessionBean#ejbActivate()
     */
    public void ejbActivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbPassivate()
     */
    public void ejbPassivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbRemove()
     */
    public void ejbRemove() {
    }

    // </editor-fold>;
    /**
     * See section 7.10.3 of the EJB 2.0 specification
     * See section 7.11.3 of the EJB 2.1 specification
     */
    public void ejbCreate() {
        // TODO implement ejbCreate if necessary, acquire resources
        // This method has access to the JNDI context so resource aquisition
        // spanning all methods can be performed here such as home interfaces
        // and data sources.
    }

    public Collection getByField(String type, String field, String val) throws RemoteException {
        if (val.isEmpty()) return new ArrayList();
        Connection conn = null;
        Statement querry = null;
        ResultSet ans = null;
        try {
            Long obj_type_id = getObjectTypeID(type);
            Long attr_type_id = getAttributeID(field, obj_type_id);
            conn = getConnection();
            querry = conn.createStatement();
            String SQLQuerry = "SELECT " + Consts.OBJECTS__OBJECT_ID 
                    + " FROM "+Consts.OBJECTS__TABLENAME
                    + " WHERE "
                    //фильтруем объекты определенного типа
                    + Consts.OBJECTS__OBJECT_TYPE_ID+"="+obj_type_id+" AND "
                    //фильтруем объекты по значению поля
                    + Consts.OBJECTS__OBJECT_ID +
                    " IN "
                    + "(SELECT "+Consts.PARAMS__OBJECT_ID+" FROM "+Consts.PARAMS_TABLENAME+" WHERE "
                    + Consts.PARAMS__ATTRIBUTE_ID +"="+attr_type_id+" AND "
                    + "UPPER("+Consts.PARAMS__VALUE+") LIKE '%"
                    + val.replaceAll("%", "/%").replaceAll("_", "/_").toUpperCase()
                    + "%' ESCAPE '/' "
                    + ")";
            ans = querry.executeQuery(SQLQuerry);
            Collection list = new ArrayList();
            while (ans.next()){
                list.add(new Long(ans.getLong(Consts.OBJECTS__OBJECT_ID)));
            }
            return list;
        } catch (FinderException ex) {
            ex.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                ans.close();
                querry.close();
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private Long getObjectTypeID(String name) throws RemoteException {
        {
            try {
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    ans = query.executeQuery("SELECT * FROM OBJECT_TYPES "
                            + "WHERE NAME='" + name + "'");
                    if (ans.next()) {
                        return new Long(ans.getLong(Consts.OBJECT_TYPES__ID));
                    } else {
                        throw new RemoteException("SecB.getObjTypeID: not such object_type " + name);
                    }
                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            } catch (Exception e) {
                throw new RemoteException("SecB.getObjTypeID: " + e.getLocalizedMessage());
            }
        }
    }

    private Long getAttributeID(String name, Long object_type_id) throws FinderException {

        try {
            Connection conn = getConnection();
            ResultSet ans = null;
            Statement query = null;
            try {
                query = conn.createStatement();
                ans = query.executeQuery("SELECT * FROM ATTRIBUTES "
                        + "WHERE OBJECT_TYPE_ID='" + object_type_id + "' AND "
                        + "NAME='" + name + "'");
                if (ans.next()) {
                    return new Long(ans.getLong(Consts.ATTRIBUTES__ATTRIBUTE_ID));
                } else {
                    throw new FinderException("not attr_id for " + name);
                }
            } finally {
                if (ans != null) {
                    ans.close();
                }
                if (query != null) {
                    query.close();
                }
                conn.close();
            }
        } catch (Exception e) {
            throw new EJBException("getAttr_id: " + e.toString());
        }
    }

    private Connection getConnection() throws NamingException, SQLException {
        DataSource ds = (DataSource) (new InitialContext()).lookup("oracle/SOS");
        return ds.getConnection();
    }
    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method" or "Web Service > Add Operation")
}
